package jdbc.note8_5;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class StudentDao implements StudentDaoI {

	@Override
	public int delete(long stuno) {
		String sql = "delete from s_student where stuno = ?";
		Connection con = DBUtil.getCon();
		PreparedStatement pstm = null;
		int flag = 0;
		try {
			pstm = con.prepareStatement(sql);
			pstm.setLong(1, stuno);
			flag = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(pstm);
			DBUtil.close(con);
		}
		return flag;
	}

	@Override
	public int update(Student stu) {
		String sql = "update s_student set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,address=?,pass=?"
				+ " where stuno=?";
		Connection con = DBUtil.getCon();
		PreparedStatement pstm = null;
		int flag = 0;
		try {
			pstm = con.prepareStatement(sql);
			pstm.setString(1, stu.getSname());
			pstm.setString(2, stu.getSex());
			pstm.setString(3, stu.getTelephone());
			pstm.setString(4, stu.getQq());
			pstm.setString(5, stu.getWechat());
			pstm.setLong(6, stu.getGradeid());
			pstm.setTimestamp(7, new Timestamp(stu.getEnterdate().getTime()));
			pstm.setString(8, stu.getFromcity());
			pstm.setTimestamp(9, new Timestamp(stu.getBirthday().getTime()));
			pstm.setString(10, stu.getAddress());
			pstm.setString(11, stu.getPass());
			pstm.setLong(12, stu.getStuno());
			flag = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(pstm);
			DBUtil.close(con);
		}

		return flag;
	}

	@Override
	public HashMap<String, Integer> add(Student stu) {
		String sql = "insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass)"
				+ " values(?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = DBUtil.getCon();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		map.put("flag", 0);
		try {
			pstm = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			pstm.setString(1, stu.getSname());
			pstm.setString(2, stu.getSex());
			pstm.setString(3, stu.getTelephone());
			pstm.setString(4, stu.getQq());
			pstm.setString(5, stu.getWechat());
			pstm.setLong(6, stu.getGradeid());
			pstm.setTimestamp(7, new Timestamp(stu.getEnterdate().getTime()));
			pstm.setString(8, stu.getFromcity());
			pstm.setTimestamp(9, new Timestamp(stu.getBirthday().getTime()));
			pstm.setString(10, stu.getAddress());
			pstm.setString(11, stu.getPass());
			map.put("flag", pstm.executeUpdate());
			rs = pstm.getGeneratedKeys();
			if (rs.next()) {
				map.put("id", rs.getInt(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pstm, con);
		}

		return map;
	}

	@Override
	public List<Student> searchAll() {
		String sql = "select * from s_student";
		Connection con = DBUtil.getCon();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<>();
		try {
			pstm = con.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Student stu = setStudent(rs);
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pstm, con);
		}
		return list;
	}

	@Override
	public Student get(long stuno) {
		String sql = "select * from s_student where stuno=?";
		Connection con = DBUtil.getCon();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			pstm = con.prepareStatement(sql);
			pstm.setLong(1, stuno);
			rs = pstm.executeQuery();
			if (rs.next()) {
				stu = setStudent(rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pstm, con);
		}
		return stu;
	}

	@Override
	public List<Student> search(int pageSize, int pageNo) {// pageSize一页的大小
															// pageNo页码
		String sql = "select * from s_student limit ?,?";
		Connection con = DBUtil.getCon();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<>();
		try {
			pstm = con.prepareStatement(sql);
			pstm.setInt(1, (pageNo - 1) * pageSize);
			pstm.setInt(2, pageSize);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Student stu = setStudent(rs);
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pstm, con);
		}
		return list;
	}

	private Student setStudent(ResultSet rs) throws SQLException {
		Student stu = new Student();
		stu.setStuno(rs.getLong("stuno"));
		stu.setSname(rs.getString("sname"));
		stu.setSex(rs.getString("sex"));
		stu.setGradeid(rs.getLong("gradeid"));
		stu.setTelephone(rs.getString("telephone"));
		stu.setQq(rs.getString("qq"));
		stu.setFromcity(rs.getString("fromcity"));
		stu.setWechat(rs.getString("wechat"));
		stu.setAddress(rs.getString("address"));
		stu.setPass(rs.getString("pass"));
		stu.setBirthday(rs.getDate("birthday"));
		stu.setEnterdate(rs.getDate("enterdate"));
		return stu;
	}
}
